package DAO;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import Entity.ReferenceData;
import Entity.Services;

public class Dao_addService {
	private static Connection conn;
	private static Statement sta;
	private static ResultSet rs;

	public static void getConnection() {
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		String url = "jdbc:sqlserver://localhost:1130;"
				+ "databaseName=MockProject;";
		String user = "sa";
		String password = "12345678";
		try {
			conn = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void close() {
		if (rs != null) {
			try {
				rs.close();
				if (sta != null)
					sta.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public ArrayList<ReferenceData> getList(String postCode) {
		ArrayList<ReferenceData> list = new ArrayList<ReferenceData>();
		getConnection();
		try {
			sta = conn.createStatement();
			String sql = "Select * from ReferenceData where RefCode='"
					+ postCode + "'";
			rs = sta.executeQuery(sql);
			while (rs.next()) {
				ReferenceData rf = new ReferenceData();
				rf.setId(rs.getString("RefID"));
				rf.setRefCode(rs.getString("RefCode"));
				rf.setRefName(rs.getString("RefValue"));
				list.add(rf);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return list;

	}

	public static int getId() {
		int id = 0;
		getConnection();
		try {
			sta = conn.createStatement();
			String sql = "SELECT Max(id) AS id FROM Services ";
			rs = sta.executeQuery(sql);
			if (rs.next()) {
				id = rs.getInt("id");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close();
			id++;
			return id;
		}
	}

	public static int insert(int id, String sv_name, String sub_type,
			String contacts, String sv_description, String client_des,
			String sv_attenace, String sv_start_expect, String sv_star,
			String sv_end, int sv_ed_year, int sv_ed_month, boolean active,
			String sv_full_des, String dept_code, String sv_type,
			String sv_des_delivery, String sv_contract_code,
			String sv_contract_value, boolean contract_state, String method) {
		getConnection();
		int kq=-1;
		PreparedStatement pre_insert = null;
		String sql = "INSERT INTO Services VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			conn.setAutoCommit(false);
			pre_insert = conn.prepareStatement(sql);
			pre_insert.setInt(1, id);
			pre_insert.setString(2, sv_name);
			pre_insert.setString(3, sub_type);
			pre_insert.setString(4, contacts);
			pre_insert.setString(5, sv_description);
			pre_insert.setString(6, client_des);
			pre_insert.setString(7, sv_attenace);
			pre_insert.setString(8, sv_start_expect);
			pre_insert.setString(9, sv_star);
			pre_insert.setString(10, sv_end);
			pre_insert.setInt(11, sv_ed_year);
			pre_insert.setInt(12, sv_ed_month);
			pre_insert.setBoolean(13, active);
			pre_insert.setString(14, sv_full_des);
			pre_insert.setString(15, dept_code);
			pre_insert.setString(16, sv_type);
			pre_insert.setString(17, sv_des_delivery);
			pre_insert.setString(18, sv_contract_code);
			pre_insert.setString(19, sv_contract_value);
			pre_insert.setBoolean(20, contract_state);
			pre_insert.setString(21, method);
			kq =pre_insert.executeUpdate();
			//insert Tuan

			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			if (conn != null) {
				try {
					conn.rollback();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
			}
			e.printStackTrace();
		} finally {
			if (pre_insert != null)
				try {
					pre_insert.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return kq;
		}

	}

	public static boolean checkName(String name) {
		String sql = "select * from Services where sv_name='" + name + "'";
		getConnection();
		try {
			sta = conn.createStatement();
			rs = sta.executeQuery(sql);
			if (rs.next())
				return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	// /list
	public static ArrayList<Services> getList() {
		ArrayList<Services> ds = new ArrayList<Services>();
		getConnection();
		String sql = "select * from Services";
		try {
			sta = conn.createStatement();
			rs = sta.executeQuery(sql);
			while (rs.next()) {
				Services x = new Services();
				int id;
				id = rs.getInt(1);
				x.setId(id);
				
				String sv_name;
				sv_name = rs.getString(2);
				x.setSv_name(sv_name);
				
				String sub_type;
				sub_type = rs.getString(3);
				x.setSub_type(sub_type);
				
				String contacts;
				contacts = rs.getString(4);
				x.setContact(contacts);
				
				String sv_description;
				sv_description = rs.getString(5);
				x.setSv_description(sv_description);
				
				String client_des;
				client_des = rs.getString(6);
				x.setClient_des(client_des);
				
				String sv_attenace;
				sv_attenace = rs.getString(7);
				x.setSv_attenace(sv_attenace);
				
				String sv_start_expect;
				sv_start_expect = rs.getString(8);
				x.setSv_start_expect(sv_start_expect);
				
				String sv_start;
				sv_start = rs.getString(9);
				x.setSv_start(sv_start);
				
				String sv_end;
				sv_end = rs.getString(10);
				x.setSv_end(sv_end);
				
				int sv_ed_year;
				sv_ed_year = rs.getInt(11);
				x.setSv_ed_year(sv_ed_year);
				
				int sv_ed_month;
				sv_ed_month = rs.getInt(12);
				x.setSv_ed_month(sv_ed_month);
				
				boolean active;
				active = rs.getBoolean(13);
				x.setActive(active);
				String sv_full_des;
				sv_full_des = rs.getString(14);
				x.setSv_full_des(sv_full_des);
				
				String dept_code = rs.getString(15);
				x.setDept_code(dept_code);
				
				String sv_type = rs.getString(16);
				x.setSv_type(sv_type);
				
				String sv_des_delivery =rs.getString(17);
				x.setSv_des_delivery(sv_des_delivery);
				
				String sv_contract_code =rs.getString(18);
				x.setSv_contract_code(sv_contract_code);
				
				String sv_contract_value=rs.getString(19);
				x.setSv_contract_value(sv_contract_value);
				
				boolean contract_state = rs.getBoolean(20);
				x.setContract_state(contract_state);
				
				String method = rs.getString(21);
				x.setMethod(method);
				
				ds.add(x);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close();
		}
		return ds;
	}
	
	public static int active(String sv_name){
		getConnection();
		try {
			sta = conn.createStatement();
			String sql = "UPDATE Services SET active = 1 Where sv_name='"+sv_name+"'";
			return sta.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close();
		}
		return -1;
	}
	
	//update
		public static int updateService(int id, String sv_name, String sub_type,
				String contacts, String sv_description, String client_des,
				String sv_attenace, String sv_start_expect, String sv_star,
				String sv_end, int sv_ed_year, int sv_ed_month, boolean active,
				String sv_full_des, String dept_code, String sv_type,
				String sv_des_delivery, String sv_contract_code,
				String sv_contract_value, boolean contract_state, String method) {
			getConnection();
			PreparedStatement pre_insert = null;
			int kq=-1;
			String sql = "UPDATE Services SET sub_type = ?,lead_contact=?,sv_description=?,client_des=?" +
					",sv_attenace = ?,sv_start_expect=?,sv_start=?,sv_end=?,sv_ed_year=?,sv_ed_month=?,active=?," +
					"sv_full_des=?,dept_code=?,sv_type=?,sv_des_delivery=?,sv_contract_code=?,sv_contract_value=?" +
					",contract_state=?,method=? " +
					"WHERE sv_name = ? ";
			try {
				conn.setAutoCommit(false);
				pre_insert = conn.prepareStatement(sql);
				pre_insert.setString(1, sub_type);
				pre_insert.setString(2, contacts);
				pre_insert.setString(3, sv_description);
				pre_insert.setString(4, client_des);
				pre_insert.setString(5, sv_attenace);
				pre_insert.setString(6, sv_start_expect);
				pre_insert.setString(7, sv_star);
				pre_insert.setString(8, sv_end);
				pre_insert.setInt(9, sv_ed_year);
				pre_insert.setInt(10, sv_ed_month);
				pre_insert.setBoolean(11, active);
				pre_insert.setString(12, sv_full_des);
				pre_insert.setString(13, dept_code);
				pre_insert.setString(14, sv_type);
				pre_insert.setString(15, sv_des_delivery);
				pre_insert.setString(16, sv_contract_code);
				pre_insert.setString(17, sv_contract_value);
				pre_insert.setBoolean(18, contract_state);
				pre_insert.setString(19, method);
				pre_insert.setString(20, sv_name);
				kq = pre_insert.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				if (conn != null) {
					try {
						conn.rollback();
					} catch (SQLException e1) {
						// TODO Auto-generated catch block
						e1.printStackTrace();
					}
				}
				e.printStackTrace();
			} finally {
				if (pre_insert != null)
					try {
						pre_insert.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				try {
					conn.setAutoCommit(true);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return kq;
			}
		
		}
//	get Service
	public static Services getServices(String sv_name){
		Services sv = null;
		getConnection();
		try {
			sta = conn.createStatement();
			String sql ="select * from Services Where sv_name = '"+sv_name+"'";
			rs =sta.executeQuery(sql);
			if(rs.next()){
				int id= rs.getInt(1);
				String sv_name1=rs.getString(2);
				String sub_type=rs.getString(3);
				String contacts = rs.getString(4);
				String sv_description= rs.getString(5);
				String client_des = rs.getString(6);
				String sv_attenace = rs.getString(7);
				String sv_start_expect = rs.getString(8);
				String sv_start = rs.getString(9);
				String sv_end = rs.getString(10);
				int sv_ed_year= rs.getInt(11);
				int sv_ed_month = rs.getInt(12);
				boolean active = rs.getBoolean(13);
				String sv_full_des = rs.getString(14);
				String dept_code = rs.getString(15);
				String sv_type= rs.getString(16);
				String sv_des_delivery = rs.getString(17);
				String sv_contract_code = rs.getString(18);
				String sv_contract_value= rs.getString(19);
				boolean contract_state= rs.getBoolean(20);
				String method = rs.getString(21);
				sv = new Services(id, sv_name1, sub_type, contacts, sv_description, client_des, sv_attenace, sv_start_expect, sv_start, sv_end, sv_ed_year, sv_ed_month, active, sv_full_des, dept_code, sv_type, sv_des_delivery, sv_contract_code, sv_contract_value, contract_state, method);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close();
		}
		return sv;
	}
	
	
}
